--1
SELECT	
 REGION,PRODUCT,FEATURE, SUM(ALTCHNL_IN) ALTCHNL_IN, SUM(ALTCHNL_PCT) ALTCHNL_PCT,
		SUM(ALTCHNL_OUT) ALTCHNL_OUT, SUM(COR_IN) COR_IN, SUM(COR_PCT) COR_PCT,
		SUM(COR_OUT) COR_OUT, SUM(CS_IN) CS_IN, SUM(CS_PCT) CS_PCT, SUM(CS_OUT) CS_OUT,
		SUM(D2D_IN) D2D_IN, SUM(D2D_PCT) D2D_PCT, SUM(D2D_OUT) D2D_OUT,
		SUM(MOBILITYDMD_IN) MOBILITYDMD_IN, SUM(MOBILITYDMD_PCT) MOBILITYDMD_PCT,
		SUM(MOBILITYDMD_OUT) MOBILITYDMD_OUT, SUM(NATIONALRET_IN) NATIONALRET_IN,
		SUM(NATIONALRET_PCT) NATIONALRET_PCT, SUM(NATIONALRET_OUT) NATIONALRET_OUT,
		SUM(OBTM_IN) OBTM_IN, SUM(OBTM_PCT) OBTM_PCT, SUM(OBTM_OUT) OBTM_OUT,
		SUM(ONLINE_IN) ONLINE_IN, SUM(ONLINE_PCT) ONLINE_PCT, SUM(ONLINE_OUT) ONLINE_OUT,
		SUM(P2RCALLCEN_IN) P2RCALLCEN_IN, SUM(P2RCALLCEN_PCT) P2RCALLCEN_PCT,
		SUM(P2RCALLCEN_OUT) P2RCALLCEN_OUT, SUM(STORES_IN) STORES_IN,
		SUM(STORES_PCT) STORES_PCT, SUM(STORES_OUT) STORES_OUT, SUM(TE_IN) TE_IN,
		SUM(TE_PCT) TE_PCT, SUM(TE_OUT) TE_OUT, SUM(WEB_IN) WEB_IN, SUM(WEB_PCT) WEB_PCT,
		SUM(WEB_OUT) WEB_OUT, SUM(TOT) TOT, MAX(ROLLUP_TYPE) ROLLUP_TYPE, YR, MN
 
FROM	
(SELECT  DISTINCT Z.REGION, Z.PRODUCT,
CASE Z.FEATURE
 WHEN 'Expres' THEN 'Express'
 WHEN 'Hsia18' THEN 'Max18'
 WHEN 'BOT100' THEN 'VOIP 1000'
 WHEN 'BOT250' THEN 'VOIP 250'
 WHEN 'UNLMTD' THEN 'VOIP Unlimited'
 WHEN 'UFAM' THEN 'UFamily'
 WHEN 'U200la' THEN 'U200 Latino'
 WHEN 'Ubasic' THEN 'UBasic'
 WHEN 'Ucomp' THEN 'UComp'
 WHEN 'Udemo' THEN 'UDemo'
 ELSE Z.FEATURE
END FEATURE,
COALESCE(Y.ALTCHNL_I,0) ALTCHNL_IN, CASE WHEN TOT_I=0 THEN 0 ELSE CAST(1000*ALTCHNL_I/TOT_I AS DECIMAL(5,1))/10 END ALTCHNL_PCT, COALESCE(Y.ALTCHNL_O,0) ALTCHNL_OUT,
COALESCE(Y.COR_I,0) COR_IN, CASE WHEN TOT_I=0 THEN 0 ELSE CAST(1000*COR_I/TOT_I AS DECIMAL(5,1))/10 END COR_PCT, COALESCE(Y.COR_O,0) COR_OUT,
COALESCE(Y.CS_I,0) CS_IN, CASE WHEN TOT_I=0 THEN 0 ELSE CAST(1000*CS_I/TOT_I AS DECIMAL(5,1))/10 END CS_PCT, COALESCE(Y.CS_O,0) CS_OUT,
COALESCE(Y.D2D_I,0) D2D_IN, CASE WHEN TOT_I=0 THEN 0 ELSE CAST(1000*D2D_I/TOT_I AS DECIMAL(5,1))/10 END D2D_PCT, COALESCE(Y.D2D_O,0) D2D_OUT,
COALESCE(Y.MOBILITYDMD_I,0) MOBILITYDMD_IN, CASE WHEN TOT_I=0 THEN 0 ELSE CAST(1000*MOBILITYDMD_I/TOT_I AS DECIMAL(5,1))/10 END MOBILITYDMD_PCT, COALESCE(Y.MOBILITYDMD_O,0) MOBILITYDMD_OUT,
COALESCE(Y.NATIONALRET_I,0) NATIONALRET_IN, CASE WHEN TOT_I=0 THEN 0 ELSE CAST(1000*NATIONALRET_I/TOT_I AS DECIMAL(5,1))/10 END NATIONALRET_PCT, COALESCE(Y.NATIONALRET_O,0) NATIONALRET_OUT,
COALESCE(Y.OBTM_I,0) OBTM_IN, CASE WHEN TOT_I=0 THEN 0 ELSE CAST(1000*OBTM_I/TOT_I AS DECIMAL(5,1))/10 END OBTM_PCT, COALESCE(Y.OBTM_O,0) OBTM_OUT,
COALESCE(Y.ONLINE_I,0) ONLINE_IN, CASE WHEN TOT_I=0 THEN 0 ELSE CAST(1000*ONLINE_I/TOT_I AS DECIMAL(5,1))/10 END ONLINE_PCT, COALESCE(Y.ONLINE_O,0) ONLINE_OUT,
COALESCE(Y.P2RCALLCEN_I,0) P2RCALLCEN_IN, CASE WHEN TOT_I=0 THEN 0 ELSE CAST(1000*P2RCALLCEN_I/TOT_I AS DECIMAL(5,1))/10 END P2RCALLCEN_PCT, COALESCE(Y.P2RCALLCEN_O,0) P2RCALLCEN_OUT,
COALESCE(Y.STORES_I,0) STORES_IN, CASE WHEN TOT_I=0 THEN 0 ELSE CAST(1000*STORES_I/TOT_I AS DECIMAL(5,1))/10 END STORES_PCT, COALESCE(Y.STORES_O,0) STORES_OUT,
COALESCE(Y.TE_I,0) TE_IN, CASE WHEN TOT_I=0 THEN 0 ELSE CAST(1000*TE_I/TOT_I AS DECIMAL(5,1))/10 END TE_PCT, COALESCE(Y.TE_O,0) TE_OUT,
COALESCE(Y.WEB_I,0) WEB_IN, CASE WHEN TOT_I=0 THEN 0 ELSE CAST(1000*WEB_I/TOT_I AS DECIMAL(5,1))/10 END WEB_PCT, COALESCE(Y.WEB_O,0) WEB_OUT,
COALESCE(TOT_I,0)  + COALESCE(TOT_O,0)  TOT,
'REGION' ROLLUP_TYPE, YR, MN
FROM (
SELECT B.REGION, A.PRODUCT, A.FEATURE 
FROM (SELECT PRODUCT, FEATURE FROM MIS.ACC_RAW WHERE FEATURE NOT IN ('000','BVSTAN') GROUP BY 1,2) A,
     (SELECT AREA_MGR, REGION FROM MIS.ACC_BASE_DATA WHERE REGION GT '' GROUP BY 1,2) B) Z
LEFT OUTER JOIN (
SELECT B.REGION, A.PRODUCT, A.FEATURE,
 SUM(CASE WHEN A.DIRECTION='IN' AND A.SALES_CHANNEL_CD='ALT CHNL' THEN 1 ELSE 0 END) ALTCHNL_I,
 SUM(CASE WHEN A.DIRECTION='OUT' AND A.SALES_CHANNEL_CD='ALT CHNL' THEN 1 ELSE 0 END) ALTCHNL_O,
 SUM(CASE WHEN A.DIRECTION='IN' AND A.SALES_CHANNEL_CD='COR' THEN 1 ELSE 0 END) COR_I,
 SUM(CASE WHEN A.DIRECTION='OUT' AND A.SALES_CHANNEL_CD='COR' THEN 1 ELSE 0 END) COR_O,
 SUM(CASE WHEN A.DIRECTION='IN' AND A.SALES_CHANNEL_CD='CS' THEN 1 ELSE 0 END) CS_I,
 SUM(CASE WHEN A.DIRECTION='OUT' AND A.SALES_CHANNEL_CD='CS' THEN 1 ELSE 0 END) CS_O,
 SUM(CASE WHEN A.DIRECTION='IN' AND A.SALES_CHANNEL_CD='D2D' THEN 1 ELSE 0 END) D2D_I,
 SUM(CASE WHEN A.DIRECTION='OUT' AND A.SALES_CHANNEL_CD='D2D' THEN 1 ELSE 0 END) D2D_O,
 SUM(CASE WHEN A.DIRECTION='IN' AND A.SALES_CHANNEL_CD='MOBILITY DMD' THEN 1 ELSE 0 END) MOBILITYDMD_I,
 SUM(CASE WHEN A.DIRECTION='OUT' AND A.SALES_CHANNEL_CD='MOBILITY DMD' THEN 1 ELSE 0 END) MOBILITYDMD_O,
 SUM(CASE WHEN A.DIRECTION='IN' AND A.SALES_CHANNEL_CD='NATIONAL RET' THEN 1 ELSE 0 END) NATIONALRET_I,
 SUM(CASE WHEN A.DIRECTION='OUT' AND A.SALES_CHANNEL_CD='NATIONAL RET' THEN 1 ELSE 0 END) NATIONALRET_O,
 SUM(CASE WHEN A.DIRECTION='IN' AND A.SALES_CHANNEL_CD='OBTM' THEN 1 ELSE 0 END) OBTM_I,
 SUM(CASE WHEN A.DIRECTION='OUT' AND A.SALES_CHANNEL_CD='OBTM' THEN 1 ELSE 0 END) OBTM_O,
 SUM(CASE WHEN A.DIRECTION='IN' AND A.SALES_CHANNEL_CD='ONLINE' THEN 1 ELSE 0 END) ONLINE_I,
 SUM(CASE WHEN A.DIRECTION='OUT' AND A.SALES_CHANNEL_CD='ONLINE' THEN 1 ELSE 0 END) ONLINE_O,
 SUM(CASE WHEN A.DIRECTION='IN' AND A.SALES_CHANNEL_CD='P2R CALL CEN' THEN 1 ELSE 0 END) P2RCALLCEN_I,
 SUM(CASE WHEN A.DIRECTION='OUT' AND A.SALES_CHANNEL_CD='P2R CALL CEN' THEN 1 ELSE 0 END) P2RCALLCEN_O,
 SUM(CASE WHEN A.DIRECTION='IN' AND A.SALES_CHANNEL_CD='STORES' THEN 1 ELSE 0 END) STORES_I,
 SUM(CASE WHEN A.DIRECTION='OUT' AND A.SALES_CHANNEL_CD='STORES' THEN 1 ELSE 0 END) STORES_O,
 SUM(CASE WHEN A.DIRECTION='IN' AND A.SALES_CHANNEL_CD='TE' THEN 1 ELSE 0 END) TE_I,
 SUM(CASE WHEN A.DIRECTION='OUT' AND A.SALES_CHANNEL_CD='TE' THEN 1 ELSE 0 END) TE_O,
 SUM(CASE WHEN A.DIRECTION='IN' AND A.SALES_CHANNEL_CD='WEB' THEN 1 ELSE 0 END) WEB_I,
 SUM(CASE WHEN A.DIRECTION='OUT' AND A.SALES_CHANNEL_CD='WEB' THEN 1 ELSE 0 END) WEB_O,
 TOT_I-(ALTCHNL_I+COR_I+CS_I+D2D_I+MOBILITYDMD_I+NATIONALRET_I+OBTM_I+ONLINE_I+P2RCALLCEN_I+STORES_I+TE_I+WEB_I) OTH_I,
 SUM(CASE WHEN A.DIRECTION='IN' THEN 1 ELSE 0 END) TOT_I,
 SUM(CASE WHEN A.DIRECTION='OUT' THEN 1 ELSE 0 END) TOT_O,
 D.YR, D.MN
FROM MIS.ACC_RAW A
INNER JOIN MIS.ACC_CAL2  D ON A.START_DT = D.DT
INNER JOIN MIS.ACC_BASE_DATA B ON A.TRAC_ID=B.TRAC_ID
GROUP BY 1, D.YR, D.MN,2,3) Y ON Z.REGION=Y.REGION AND Z.PRODUCT=Y.PRODUCT AND Z.FEATURE=Y.FEATURE
) sums
WHERE TOT>0
GROUP	BY 1, YR, MN, ROLLUP (2,3)
ORDER	BY 1, YR DESC,MN DESC,2,3,4;

--2
SELECT	
 MARKET,PRODUCT,FEATURE, SUM(ALTCHNL_IN) ALTCHNL_IN, SUM(ALTCHNL_PCT) ALTCHNL_PCT,
		SUM(ALTCHNL_OUT) ALTCHNL_OUT, SUM(COR_IN) COR_IN, SUM(COR_PCT) COR_PCT,
		SUM(COR_OUT) COR_OUT, SUM(CS_IN) CS_IN, SUM(CS_PCT) CS_PCT, SUM(CS_OUT) CS_OUT,
		SUM(D2D_IN) D2D_IN, SUM(D2D_PCT) D2D_PCT, SUM(D2D_OUT) D2D_OUT,
		SUM(MOBILITYDMD_IN) MOBILITYDMD_IN, SUM(MOBILITYDMD_PCT) MOBILITYDMD_PCT,
		SUM(MOBILITYDMD_OUT) MOBILITYDMD_OUT, SUM(NATIONALRET_IN) NATIONALRET_IN,
		SUM(NATIONALRET_PCT) NATIONALRET_PCT, SUM(NATIONALRET_OUT) NATIONALRET_OUT,
		SUM(OBTM_IN) OBTM_IN, SUM(OBTM_PCT) OBTM_PCT, SUM(OBTM_OUT) OBTM_OUT,
		SUM(ONLINE_IN) ONLINE_IN, SUM(ONLINE_PCT) ONLINE_PCT, SUM(ONLINE_OUT) ONLINE_OUT,
		SUM(P2RCALLCEN_IN) P2RCALLCEN_IN, SUM(P2RCALLCEN_PCT) P2RCALLCEN_PCT,
		SUM(P2RCALLCEN_OUT) P2RCALLCEN_OUT, SUM(STORES_IN) STORES_IN,
		SUM(STORES_PCT) STORES_PCT, SUM(STORES_OUT) STORES_OUT, SUM(TE_IN) TE_IN,
		SUM(TE_PCT) TE_PCT, SUM(TE_OUT) TE_OUT, SUM(WEB_IN) WEB_IN, SUM(WEB_PCT) WEB_PCT,
		SUM(WEB_OUT) WEB_OUT, SUM(TOT) TOT, MAX(ROLLUP_TYPE) ROLLUP_TYPE, YR, MN
 
FROM	
(SELECT  DISTINCT Z.MARKET, Z.PRODUCT,
CASE Z.FEATURE
 WHEN 'Expres' THEN 'Express'
 WHEN 'Hsia18' THEN 'Max18'
 WHEN 'BOT100' THEN 'VOIP 1000'
 WHEN 'BOT250' THEN 'VOIP 250'
 WHEN 'UNLMTD' THEN 'VOIP Unlimited'
 WHEN 'UFAM' THEN 'UFamily'
 WHEN 'U200la' THEN 'U200 Latino'
 WHEN 'Ubasic' THEN 'UBasic'
 WHEN 'Ucomp' THEN 'UComp'
 WHEN 'Udemo' THEN 'UDemo'
 ELSE Z.FEATURE
END FEATURE,
COALESCE(Y.ALTCHNL_I,0) ALTCHNL_IN, CASE WHEN TOT_I=0 THEN 0 ELSE CAST(1000*ALTCHNL_I/TOT_I AS DECIMAL(5,1))/10 END ALTCHNL_PCT, COALESCE(Y.ALTCHNL_O,0) ALTCHNL_OUT,
COALESCE(Y.COR_I,0) COR_IN, CASE WHEN TOT_I=0 THEN 0 ELSE CAST(1000*COR_I/TOT_I AS DECIMAL(5,1))/10 END COR_PCT, COALESCE(Y.COR_O,0) COR_OUT,
COALESCE(Y.CS_I,0) CS_IN, CASE WHEN TOT_I=0 THEN 0 ELSE CAST(1000*CS_I/TOT_I AS DECIMAL(5,1))/10 END CS_PCT, COALESCE(Y.CS_O,0) CS_OUT,
COALESCE(Y.D2D_I,0) D2D_IN, CASE WHEN TOT_I=0 THEN 0 ELSE CAST(1000*D2D_I/TOT_I AS DECIMAL(5,1))/10 END D2D_PCT, COALESCE(Y.D2D_O,0) D2D_OUT,
COALESCE(Y.MOBILITYDMD_I,0) MOBILITYDMD_IN, CASE WHEN TOT_I=0 THEN 0 ELSE CAST(1000*MOBILITYDMD_I/TOT_I AS DECIMAL(5,1))/10 END MOBILITYDMD_PCT, COALESCE(Y.MOBILITYDMD_O,0) MOBILITYDMD_OUT,
COALESCE(Y.NATIONALRET_I,0) NATIONALRET_IN, CASE WHEN TOT_I=0 THEN 0 ELSE CAST(1000*NATIONALRET_I/TOT_I AS DECIMAL(5,1))/10 END NATIONALRET_PCT, COALESCE(Y.NATIONALRET_O,0) NATIONALRET_OUT,
COALESCE(Y.OBTM_I,0) OBTM_IN, CASE WHEN TOT_I=0 THEN 0 ELSE CAST(1000*OBTM_I/TOT_I AS DECIMAL(5,1))/10 END OBTM_PCT, COALESCE(Y.OBTM_O,0) OBTM_OUT,
COALESCE(Y.ONLINE_I,0) ONLINE_IN, CASE WHEN TOT_I=0 THEN 0 ELSE CAST(1000*ONLINE_I/TOT_I AS DECIMAL(5,1))/10 END ONLINE_PCT, COALESCE(Y.ONLINE_O,0) ONLINE_OUT,
COALESCE(Y.P2RCALLCEN_I,0) P2RCALLCEN_IN, CASE WHEN TOT_I=0 THEN 0 ELSE CAST(1000*P2RCALLCEN_I/TOT_I AS DECIMAL(5,1))/10 END P2RCALLCEN_PCT, COALESCE(Y.P2RCALLCEN_O,0) P2RCALLCEN_OUT,
COALESCE(Y.STORES_I,0) STORES_IN, CASE WHEN TOT_I=0 THEN 0 ELSE CAST(1000*STORES_I/TOT_I AS DECIMAL(5,1))/10 END STORES_PCT, COALESCE(Y.STORES_O,0) STORES_OUT,
COALESCE(Y.TE_I,0) TE_IN, CASE WHEN TOT_I=0 THEN 0 ELSE CAST(1000*TE_I/TOT_I AS DECIMAL(5,1))/10 END TE_PCT, COALESCE(Y.TE_O,0) TE_OUT,
COALESCE(Y.WEB_I,0) WEB_IN, CASE WHEN TOT_I=0 THEN 0 ELSE CAST(1000*WEB_I/TOT_I AS DECIMAL(5,1))/10 END WEB_PCT, COALESCE(Y.WEB_O,0) WEB_OUT,
COALESCE(TOT_I,0)  + COALESCE(TOT_O,0)  TOT,
'MARKET' ROLLUP_TYPE, YR, MN
FROM (
SELECT B.MARKET, A.PRODUCT, A.FEATURE 
FROM (SELECT PRODUCT, FEATURE FROM MIS.ACC_RAW WHERE FEATURE NOT IN ('000','BVSTAN') GROUP BY 1,2) A,
     (SELECT AREA_MGR, MARKET FROM MIS.ACC_BASE_DATA WHERE MARKET GT '' GROUP BY 1,2) B) Z
LEFT OUTER JOIN (
SELECT B.MARKET, A.PRODUCT, A.FEATURE,
 SUM(CASE WHEN A.DIRECTION='IN' AND A.SALES_CHANNEL_CD='ALT CHNL' THEN 1 ELSE 0 END) ALTCHNL_I,
 SUM(CASE WHEN A.DIRECTION='OUT' AND A.SALES_CHANNEL_CD='ALT CHNL' THEN 1 ELSE 0 END) ALTCHNL_O,
 SUM(CASE WHEN A.DIRECTION='IN' AND A.SALES_CHANNEL_CD='COR' THEN 1 ELSE 0 END) COR_I,
 SUM(CASE WHEN A.DIRECTION='OUT' AND A.SALES_CHANNEL_CD='COR' THEN 1 ELSE 0 END) COR_O,
 SUM(CASE WHEN A.DIRECTION='IN' AND A.SALES_CHANNEL_CD='CS' THEN 1 ELSE 0 END) CS_I,
 SUM(CASE WHEN A.DIRECTION='OUT' AND A.SALES_CHANNEL_CD='CS' THEN 1 ELSE 0 END) CS_O,
 SUM(CASE WHEN A.DIRECTION='IN' AND A.SALES_CHANNEL_CD='D2D' THEN 1 ELSE 0 END) D2D_I,
 SUM(CASE WHEN A.DIRECTION='OUT' AND A.SALES_CHANNEL_CD='D2D' THEN 1 ELSE 0 END) D2D_O,
 SUM(CASE WHEN A.DIRECTION='IN' AND A.SALES_CHANNEL_CD='MOBILITY DMD' THEN 1 ELSE 0 END) MOBILITYDMD_I,
 SUM(CASE WHEN A.DIRECTION='OUT' AND A.SALES_CHANNEL_CD='MOBILITY DMD' THEN 1 ELSE 0 END) MOBILITYDMD_O,
 SUM(CASE WHEN A.DIRECTION='IN' AND A.SALES_CHANNEL_CD='NATIONAL RET' THEN 1 ELSE 0 END) NATIONALRET_I,
 SUM(CASE WHEN A.DIRECTION='OUT' AND A.SALES_CHANNEL_CD='NATIONAL RET' THEN 1 ELSE 0 END) NATIONALRET_O,
 SUM(CASE WHEN A.DIRECTION='IN' AND A.SALES_CHANNEL_CD='OBTM' THEN 1 ELSE 0 END) OBTM_I,
 SUM(CASE WHEN A.DIRECTION='OUT' AND A.SALES_CHANNEL_CD='OBTM' THEN 1 ELSE 0 END) OBTM_O,
 SUM(CASE WHEN A.DIRECTION='IN' AND A.SALES_CHANNEL_CD='ONLINE' THEN 1 ELSE 0 END) ONLINE_I,
 SUM(CASE WHEN A.DIRECTION='OUT' AND A.SALES_CHANNEL_CD='ONLINE' THEN 1 ELSE 0 END) ONLINE_O,
 SUM(CASE WHEN A.DIRECTION='IN' AND A.SALES_CHANNEL_CD='P2R CALL CEN' THEN 1 ELSE 0 END) P2RCALLCEN_I,
 SUM(CASE WHEN A.DIRECTION='OUT' AND A.SALES_CHANNEL_CD='P2R CALL CEN' THEN 1 ELSE 0 END) P2RCALLCEN_O,
 SUM(CASE WHEN A.DIRECTION='IN' AND A.SALES_CHANNEL_CD='STORES' THEN 1 ELSE 0 END) STORES_I,
 SUM(CASE WHEN A.DIRECTION='OUT' AND A.SALES_CHANNEL_CD='STORES' THEN 1 ELSE 0 END) STORES_O,
 SUM(CASE WHEN A.DIRECTION='IN' AND A.SALES_CHANNEL_CD='TE' THEN 1 ELSE 0 END) TE_I,
 SUM(CASE WHEN A.DIRECTION='OUT' AND A.SALES_CHANNEL_CD='TE' THEN 1 ELSE 0 END) TE_O,
 SUM(CASE WHEN A.DIRECTION='IN' AND A.SALES_CHANNEL_CD='WEB' THEN 1 ELSE 0 END) WEB_I,
 SUM(CASE WHEN A.DIRECTION='OUT' AND A.SALES_CHANNEL_CD='WEB' THEN 1 ELSE 0 END) WEB_O,
 TOT_I-(ALTCHNL_I+COR_I+CS_I+D2D_I+MOBILITYDMD_I+NATIONALRET_I+OBTM_I+ONLINE_I+P2RCALLCEN_I+STORES_I+TE_I+WEB_I) OTH_I,
 SUM(CASE WHEN A.DIRECTION='IN' THEN 1 ELSE 0 END) TOT_I,
 SUM(CASE WHEN A.DIRECTION='OUT' THEN 1 ELSE 0 END) TOT_O,
 D.YR, D.MN
FROM MIS.ACC_RAW A
INNER JOIN MIS.ACC_CAL2  D ON A.START_DT = D.DT
INNER JOIN MIS.ACC_BASE_DATA B ON A.TRAC_ID=B.TRAC_ID
GROUP BY 1, D.YR, D.MN,2,3) Y ON Z.MARKET=Y.MARKET AND Z.PRODUCT=Y.PRODUCT AND Z.FEATURE=Y.FEATURE
) sums
WHERE TOT>0
GROUP	BY 1, YR, MN, ROLLUP (2,3)
ORDER	BY 1, YR DESC,MN DESC,2,3,4;

--3
SELECT	
 STATE_CD,PRODUCT,FEATURE, SUM(ALTCHNL_IN) ALTCHNL_IN, SUM(ALTCHNL_PCT) ALTCHNL_PCT,
		SUM(ALTCHNL_OUT) ALTCHNL_OUT, SUM(COR_IN) COR_IN, SUM(COR_PCT) COR_PCT,
		SUM(COR_OUT) COR_OUT, SUM(CS_IN) CS_IN, SUM(CS_PCT) CS_PCT, SUM(CS_OUT) CS_OUT,
		SUM(D2D_IN) D2D_IN, SUM(D2D_PCT) D2D_PCT, SUM(D2D_OUT) D2D_OUT,
		SUM(MOBILITYDMD_IN) MOBILITYDMD_IN, SUM(MOBILITYDMD_PCT) MOBILITYDMD_PCT,
		SUM(MOBILITYDMD_OUT) MOBILITYDMD_OUT, SUM(NATIONALRET_IN) NATIONALRET_IN,
		SUM(NATIONALRET_PCT) NATIONALRET_PCT, SUM(NATIONALRET_OUT) NATIONALRET_OUT,
		SUM(OBTM_IN) OBTM_IN, SUM(OBTM_PCT) OBTM_PCT, SUM(OBTM_OUT) OBTM_OUT,
		SUM(ONLINE_IN) ONLINE_IN, SUM(ONLINE_PCT) ONLINE_PCT, SUM(ONLINE_OUT) ONLINE_OUT,
		SUM(P2RCALLCEN_IN) P2RCALLCEN_IN, SUM(P2RCALLCEN_PCT) P2RCALLCEN_PCT,
		SUM(P2RCALLCEN_OUT) P2RCALLCEN_OUT, SUM(STORES_IN) STORES_IN,
		SUM(STORES_PCT) STORES_PCT, SUM(STORES_OUT) STORES_OUT, SUM(TE_IN) TE_IN,
		SUM(TE_PCT) TE_PCT, SUM(TE_OUT) TE_OUT, SUM(WEB_IN) WEB_IN, SUM(WEB_PCT) WEB_PCT,
		SUM(WEB_OUT) WEB_OUT, SUM(TOT) TOT, MAX(ROLLUP_TYPE) ROLLUP_TYPE, YR, MN
 
FROM	
(SELECT  DISTINCT Z.STATE_CD, Z.PRODUCT,
CASE Z.FEATURE
 WHEN 'Expres' THEN 'Express'
 WHEN 'Hsia18' THEN 'Max18'
 WHEN 'BOT100' THEN 'VOIP 1000'
 WHEN 'BOT250' THEN 'VOIP 250'
 WHEN 'UNLMTD' THEN 'VOIP Unlimited'
 WHEN 'UFAM' THEN 'UFamily'
 WHEN 'U200la' THEN 'U200 Latino'
 WHEN 'Ubasic' THEN 'UBasic'
 WHEN 'Ucomp' THEN 'UComp'
 WHEN 'Udemo' THEN 'UDemo'
 ELSE Z.FEATURE
END FEATURE,
COALESCE(Y.ALTCHNL_I,0) ALTCHNL_IN, CASE WHEN TOT_I=0 THEN 0 ELSE CAST(1000*ALTCHNL_I/TOT_I AS DECIMAL(5,1))/10 END ALTCHNL_PCT, COALESCE(Y.ALTCHNL_O,0) ALTCHNL_OUT,
COALESCE(Y.COR_I,0) COR_IN, CASE WHEN TOT_I=0 THEN 0 ELSE CAST(1000*COR_I/TOT_I AS DECIMAL(5,1))/10 END COR_PCT, COALESCE(Y.COR_O,0) COR_OUT,
COALESCE(Y.CS_I,0) CS_IN, CASE WHEN TOT_I=0 THEN 0 ELSE CAST(1000*CS_I/TOT_I AS DECIMAL(5,1))/10 END CS_PCT, COALESCE(Y.CS_O,0) CS_OUT,
COALESCE(Y.D2D_I,0) D2D_IN, CASE WHEN TOT_I=0 THEN 0 ELSE CAST(1000*D2D_I/TOT_I AS DECIMAL(5,1))/10 END D2D_PCT, COALESCE(Y.D2D_O,0) D2D_OUT,
COALESCE(Y.MOBILITYDMD_I,0) MOBILITYDMD_IN, CASE WHEN TOT_I=0 THEN 0 ELSE CAST(1000*MOBILITYDMD_I/TOT_I AS DECIMAL(5,1))/10 END MOBILITYDMD_PCT, COALESCE(Y.MOBILITYDMD_O,0) MOBILITYDMD_OUT,
COALESCE(Y.NATIONALRET_I,0) NATIONALRET_IN, CASE WHEN TOT_I=0 THEN 0 ELSE CAST(1000*NATIONALRET_I/TOT_I AS DECIMAL(5,1))/10 END NATIONALRET_PCT, COALESCE(Y.NATIONALRET_O,0) NATIONALRET_OUT,
COALESCE(Y.OBTM_I,0) OBTM_IN, CASE WHEN TOT_I=0 THEN 0 ELSE CAST(1000*OBTM_I/TOT_I AS DECIMAL(5,1))/10 END OBTM_PCT, COALESCE(Y.OBTM_O,0) OBTM_OUT,
COALESCE(Y.ONLINE_I,0) ONLINE_IN, CASE WHEN TOT_I=0 THEN 0 ELSE CAST(1000*ONLINE_I/TOT_I AS DECIMAL(5,1))/10 END ONLINE_PCT, COALESCE(Y.ONLINE_O,0) ONLINE_OUT,
COALESCE(Y.P2RCALLCEN_I,0) P2RCALLCEN_IN, CASE WHEN TOT_I=0 THEN 0 ELSE CAST(1000*P2RCALLCEN_I/TOT_I AS DECIMAL(5,1))/10 END P2RCALLCEN_PCT, COALESCE(Y.P2RCALLCEN_O,0) P2RCALLCEN_OUT,
COALESCE(Y.STORES_I,0) STORES_IN, CASE WHEN TOT_I=0 THEN 0 ELSE CAST(1000*STORES_I/TOT_I AS DECIMAL(5,1))/10 END STORES_PCT, COALESCE(Y.STORES_O,0) STORES_OUT,
COALESCE(Y.TE_I,0) TE_IN, CASE WHEN TOT_I=0 THEN 0 ELSE CAST(1000*TE_I/TOT_I AS DECIMAL(5,1))/10 END TE_PCT, COALESCE(Y.TE_O,0) TE_OUT,
COALESCE(Y.WEB_I,0) WEB_IN, CASE WHEN TOT_I=0 THEN 0 ELSE CAST(1000*WEB_I/TOT_I AS DECIMAL(5,1))/10 END WEB_PCT, COALESCE(Y.WEB_O,0) WEB_OUT,
COALESCE(TOT_I,0)  + COALESCE(TOT_O,0)  TOT,
'STATE_CD' ROLLUP_TYPE, YR, MN
FROM (
SELECT B.STATE_CD, A.PRODUCT, A.FEATURE 
FROM (SELECT PRODUCT, FEATURE FROM MIS.ACC_RAW WHERE FEATURE NOT IN ('000','BVSTAN') GROUP BY 1,2) A,
     (SELECT AREA_MGR, SUBSTR(TRAC_ID,1,2) STATE_CD FROM MIS.ACC_BASE_DATA WHERE TRAC_ID GT '' GROUP BY 1,2) B) Z
LEFT OUTER JOIN (
SELECT  SUBSTR(B.TRAC_ID,1,2) STATE_CD, A.PRODUCT, A.FEATURE,
 SUM(CASE WHEN A.DIRECTION='IN' AND A.SALES_CHANNEL_CD='ALT CHNL' THEN 1 ELSE 0 END) ALTCHNL_I,
 SUM(CASE WHEN A.DIRECTION='OUT' AND A.SALES_CHANNEL_CD='ALT CHNL' THEN 1 ELSE 0 END) ALTCHNL_O,
 SUM(CASE WHEN A.DIRECTION='IN' AND A.SALES_CHANNEL_CD='COR' THEN 1 ELSE 0 END) COR_I,
 SUM(CASE WHEN A.DIRECTION='OUT' AND A.SALES_CHANNEL_CD='COR' THEN 1 ELSE 0 END) COR_O,
 SUM(CASE WHEN A.DIRECTION='IN' AND A.SALES_CHANNEL_CD='CS' THEN 1 ELSE 0 END) CS_I,
 SUM(CASE WHEN A.DIRECTION='OUT' AND A.SALES_CHANNEL_CD='CS' THEN 1 ELSE 0 END) CS_O,
 SUM(CASE WHEN A.DIRECTION='IN' AND A.SALES_CHANNEL_CD='D2D' THEN 1 ELSE 0 END) D2D_I,
 SUM(CASE WHEN A.DIRECTION='OUT' AND A.SALES_CHANNEL_CD='D2D' THEN 1 ELSE 0 END) D2D_O,
 SUM(CASE WHEN A.DIRECTION='IN' AND A.SALES_CHANNEL_CD='MOBILITY DMD' THEN 1 ELSE 0 END) MOBILITYDMD_I,
 SUM(CASE WHEN A.DIRECTION='OUT' AND A.SALES_CHANNEL_CD='MOBILITY DMD' THEN 1 ELSE 0 END) MOBILITYDMD_O,
 SUM(CASE WHEN A.DIRECTION='IN' AND A.SALES_CHANNEL_CD='NATIONAL RET' THEN 1 ELSE 0 END) NATIONALRET_I,
 SUM(CASE WHEN A.DIRECTION='OUT' AND A.SALES_CHANNEL_CD='NATIONAL RET' THEN 1 ELSE 0 END) NATIONALRET_O,
 SUM(CASE WHEN A.DIRECTION='IN' AND A.SALES_CHANNEL_CD='OBTM' THEN 1 ELSE 0 END) OBTM_I,
 SUM(CASE WHEN A.DIRECTION='OUT' AND A.SALES_CHANNEL_CD='OBTM' THEN 1 ELSE 0 END) OBTM_O,
 SUM(CASE WHEN A.DIRECTION='IN' AND A.SALES_CHANNEL_CD='ONLINE' THEN 1 ELSE 0 END) ONLINE_I,
 SUM(CASE WHEN A.DIRECTION='OUT' AND A.SALES_CHANNEL_CD='ONLINE' THEN 1 ELSE 0 END) ONLINE_O,
 SUM(CASE WHEN A.DIRECTION='IN' AND A.SALES_CHANNEL_CD='P2R CALL CEN' THEN 1 ELSE 0 END) P2RCALLCEN_I,
 SUM(CASE WHEN A.DIRECTION='OUT' AND A.SALES_CHANNEL_CD='P2R CALL CEN' THEN 1 ELSE 0 END) P2RCALLCEN_O,
 SUM(CASE WHEN A.DIRECTION='IN' AND A.SALES_CHANNEL_CD='STORES' THEN 1 ELSE 0 END) STORES_I,
 SUM(CASE WHEN A.DIRECTION='OUT' AND A.SALES_CHANNEL_CD='STORES' THEN 1 ELSE 0 END) STORES_O,
 SUM(CASE WHEN A.DIRECTION='IN' AND A.SALES_CHANNEL_CD='TE' THEN 1 ELSE 0 END) TE_I,
 SUM(CASE WHEN A.DIRECTION='OUT' AND A.SALES_CHANNEL_CD='TE' THEN 1 ELSE 0 END) TE_O,
 SUM(CASE WHEN A.DIRECTION='IN' AND A.SALES_CHANNEL_CD='WEB' THEN 1 ELSE 0 END) WEB_I,
 SUM(CASE WHEN A.DIRECTION='OUT' AND A.SALES_CHANNEL_CD='WEB' THEN 1 ELSE 0 END) WEB_O,
 TOT_I-(ALTCHNL_I+COR_I+CS_I+D2D_I+MOBILITYDMD_I+NATIONALRET_I+OBTM_I+ONLINE_I+P2RCALLCEN_I+STORES_I+TE_I+WEB_I) OTH_I,
 SUM(CASE WHEN A.DIRECTION='IN' THEN 1 ELSE 0 END) TOT_I,
 SUM(CASE WHEN A.DIRECTION='OUT' THEN 1 ELSE 0 END) TOT_O,
 D.YR, D.MN
FROM MIS.ACC_RAW A
INNER JOIN MIS.ACC_CAL2  D ON A.START_DT = D.DT
INNER JOIN MIS.ACC_BASE_DATA B ON A.TRAC_ID=B.TRAC_ID
GROUP BY 1, D.YR, D.MN,2,3) Y ON Z.STATE_CD=Y.STATE_CD AND Z.PRODUCT=Y.PRODUCT AND Z.FEATURE=Y.FEATURE
) sums
WHERE TOT>0
GROUP	BY 1, YR, MN, ROLLUP (2,3)
ORDER	BY 1, YR DESC,MN DESC,2,3,4;

--4
SELECT	
AREA_MGR,PRODUCT,FEATURE, SUM(ALTCHNL_IN) ALTCHNL_IN, SUM(ALTCHNL_PCT) ALTCHNL_PCT,
		SUM(ALTCHNL_OUT) ALTCHNL_OUT, SUM(COR_IN) COR_IN, SUM(COR_PCT) COR_PCT,
		SUM(COR_OUT) COR_OUT, SUM(CS_IN) CS_IN, SUM(CS_PCT) CS_PCT, SUM(CS_OUT) CS_OUT,
		SUM(D2D_IN) D2D_IN, SUM(D2D_PCT) D2D_PCT, SUM(D2D_OUT) D2D_OUT,
		SUM(MOBILITYDMD_IN) MOBILITYDMD_IN, SUM(MOBILITYDMD_PCT) MOBILITYDMD_PCT,
		SUM(MOBILITYDMD_OUT) MOBILITYDMD_OUT, SUM(NATIONALRET_IN) NATIONALRET_IN,
		SUM(NATIONALRET_PCT) NATIONALRET_PCT, SUM(NATIONALRET_OUT) NATIONALRET_OUT,
		SUM(OBTM_IN) OBTM_IN, SUM(OBTM_PCT) OBTM_PCT, SUM(OBTM_OUT) OBTM_OUT,
		SUM(ONLINE_IN) ONLINE_IN, SUM(ONLINE_PCT) ONLINE_PCT, SUM(ONLINE_OUT) ONLINE_OUT,
		SUM(P2RCALLCEN_IN) P2RCALLCEN_IN, SUM(P2RCALLCEN_PCT) P2RCALLCEN_PCT,
		SUM(P2RCALLCEN_OUT) P2RCALLCEN_OUT, SUM(STORES_IN) STORES_IN,
		SUM(STORES_PCT) STORES_PCT, SUM(STORES_OUT) STORES_OUT, SUM(TE_IN) TE_IN,
		SUM(TE_PCT) TE_PCT, SUM(TE_OUT) TE_OUT, SUM(WEB_IN) WEB_IN, SUM(WEB_PCT) WEB_PCT,
		SUM(WEB_OUT) WEB_OUT, SUM(TOT) TOT, MAX(ROLLUP_TYPE) ROLLUP_TYPE, YR, MN
 
FROM	
(SELECT Z.AREA_MGR, Z.PRODUCT,
CASE Z.FEATURE
 WHEN 'Expres' THEN 'Express'
 WHEN 'Hsia18' THEN 'Max18'
 WHEN 'BOT100' THEN 'VOIP 1000'
 WHEN 'BOT250' THEN 'VOIP 250'
 WHEN 'UNLMTD' THEN 'VOIP Unlimited'
 WHEN 'UFAM' THEN 'UFamily'
 WHEN 'U200la' THEN 'U200 Latino'
 WHEN 'Ubasic' THEN 'UBasic'
 WHEN 'Ucomp' THEN 'UComp'
 WHEN 'Udemo' THEN 'UDemo'
 ELSE Z.FEATURE
END FEATURE,
COALESCE(Y.ALTCHNL_I,0) ALTCHNL_IN, CASE WHEN TOT_I=0 THEN 0 ELSE CAST(1000*ALTCHNL_I/TOT_I AS DECIMAL(5,1))/10 END ALTCHNL_PCT, COALESCE(Y.ALTCHNL_O,0) ALTCHNL_OUT,
COALESCE(Y.COR_I,0) COR_IN, CASE WHEN TOT_I=0 THEN 0 ELSE CAST(1000*COR_I/TOT_I AS DECIMAL(5,1))/10 END COR_PCT, COALESCE(Y.COR_O,0) COR_OUT,
COALESCE(Y.CS_I,0) CS_IN, CASE WHEN TOT_I=0 THEN 0 ELSE CAST(1000*CS_I/TOT_I AS DECIMAL(5,1))/10 END CS_PCT, COALESCE(Y.CS_O,0) CS_OUT,
COALESCE(Y.D2D_I,0) D2D_IN, CASE WHEN TOT_I=0 THEN 0 ELSE CAST(1000*D2D_I/TOT_I AS DECIMAL(5,1))/10 END D2D_PCT, COALESCE(Y.D2D_O,0) D2D_OUT,
COALESCE(Y.MOBILITYDMD_I,0) MOBILITYDMD_IN, CASE WHEN TOT_I=0 THEN 0 ELSE CAST(1000*MOBILITYDMD_I/TOT_I AS DECIMAL(5,1))/10 END MOBILITYDMD_PCT, COALESCE(Y.MOBILITYDMD_O,0) MOBILITYDMD_OUT,
COALESCE(Y.NATIONALRET_I,0) NATIONALRET_IN, CASE WHEN TOT_I=0 THEN 0 ELSE CAST(1000*NATIONALRET_I/TOT_I AS DECIMAL(5,1))/10 END NATIONALRET_PCT, COALESCE(Y.NATIONALRET_O,0) NATIONALRET_OUT,
COALESCE(Y.OBTM_I,0) OBTM_IN, CASE WHEN TOT_I=0 THEN 0 ELSE CAST(1000*OBTM_I/TOT_I AS DECIMAL(5,1))/10 END OBTM_PCT, COALESCE(Y.OBTM_O,0) OBTM_OUT,
COALESCE(Y.ONLINE_I,0) ONLINE_IN, CASE WHEN TOT_I=0 THEN 0 ELSE CAST(1000*ONLINE_I/TOT_I AS DECIMAL(5,1))/10 END ONLINE_PCT, COALESCE(Y.ONLINE_O,0) ONLINE_OUT,
COALESCE(Y.P2RCALLCEN_I,0) P2RCALLCEN_IN, CASE WHEN TOT_I=0 THEN 0 ELSE CAST(1000*P2RCALLCEN_I/TOT_I AS DECIMAL(5,1))/10 END P2RCALLCEN_PCT, COALESCE(Y.P2RCALLCEN_O,0) P2RCALLCEN_OUT,
COALESCE(Y.STORES_I,0) STORES_IN, CASE WHEN TOT_I=0 THEN 0 ELSE CAST(1000*STORES_I/TOT_I AS DECIMAL(5,1))/10 END STORES_PCT, COALESCE(Y.STORES_O,0) STORES_OUT,
COALESCE(Y.TE_I,0) TE_IN, CASE WHEN TOT_I=0 THEN 0 ELSE CAST(1000*TE_I/TOT_I AS DECIMAL(5,1))/10 END TE_PCT, COALESCE(Y.TE_O,0) TE_OUT,
COALESCE(Y.WEB_I,0) WEB_IN, CASE WHEN TOT_I=0 THEN 0 ELSE CAST(1000*WEB_I/TOT_I AS DECIMAL(5,1))/10 END WEB_PCT, COALESCE(Y.WEB_O,0) WEB_OUT,
COALESCE(TOT_I,0)  + COALESCE(TOT_O,0)  TOT,
'AREA_MGR' ROLLUP_TYPE, YR, MN
FROM (
SELECT B.AREA_MGR, A.PRODUCT, A.FEATURE 
FROM (SELECT PRODUCT, FEATURE FROM MIS.ACC_RAW WHERE FEATURE NOT IN ('000','BVSTAN') GROUP BY 1,2) A,
     (SELECT AREA_MGR FROM MIS.ACC_BASE_DATA WHERE AREA_MGR GT'' GROUP BY 1) B) Z
LEFT OUTER JOIN (
SELECT B.AREA_MGR, A.PRODUCT, A.FEATURE,
 SUM(CASE WHEN A.DIRECTION='IN' AND A.SALES_CHANNEL_CD='ALT CHNL' THEN 1 ELSE 0 END) ALTCHNL_I,
 SUM(CASE WHEN A.DIRECTION='OUT' AND A.SALES_CHANNEL_CD='ALT CHNL' THEN 1 ELSE 0 END) ALTCHNL_O,
 SUM(CASE WHEN A.DIRECTION='IN' AND A.SALES_CHANNEL_CD='COR' THEN 1 ELSE 0 END) COR_I,
 SUM(CASE WHEN A.DIRECTION='OUT' AND A.SALES_CHANNEL_CD='COR' THEN 1 ELSE 0 END) COR_O,
 SUM(CASE WHEN A.DIRECTION='IN' AND A.SALES_CHANNEL_CD='CS' THEN 1 ELSE 0 END) CS_I,
 SUM(CASE WHEN A.DIRECTION='OUT' AND A.SALES_CHANNEL_CD='CS' THEN 1 ELSE 0 END) CS_O,
 SUM(CASE WHEN A.DIRECTION='IN' AND A.SALES_CHANNEL_CD='D2D' THEN 1 ELSE 0 END) D2D_I,
 SUM(CASE WHEN A.DIRECTION='OUT' AND A.SALES_CHANNEL_CD='D2D' THEN 1 ELSE 0 END) D2D_O,
 SUM(CASE WHEN A.DIRECTION='IN' AND A.SALES_CHANNEL_CD='MOBILITY DMD' THEN 1 ELSE 0 END) MOBILITYDMD_I,
 SUM(CASE WHEN A.DIRECTION='OUT' AND A.SALES_CHANNEL_CD='MOBILITY DMD' THEN 1 ELSE 0 END) MOBILITYDMD_O,
 SUM(CASE WHEN A.DIRECTION='IN' AND A.SALES_CHANNEL_CD='NATIONAL RET' THEN 1 ELSE 0 END) NATIONALRET_I,
 SUM(CASE WHEN A.DIRECTION='OUT' AND A.SALES_CHANNEL_CD='NATIONAL RET' THEN 1 ELSE 0 END) NATIONALRET_O,
 SUM(CASE WHEN A.DIRECTION='IN' AND A.SALES_CHANNEL_CD='OBTM' THEN 1 ELSE 0 END) OBTM_I,
 SUM(CASE WHEN A.DIRECTION='OUT' AND A.SALES_CHANNEL_CD='OBTM' THEN 1 ELSE 0 END) OBTM_O,
 SUM(CASE WHEN A.DIRECTION='IN' AND A.SALES_CHANNEL_CD='ONLINE' THEN 1 ELSE 0 END) ONLINE_I,
 SUM(CASE WHEN A.DIRECTION='OUT' AND A.SALES_CHANNEL_CD='ONLINE' THEN 1 ELSE 0 END) ONLINE_O,
 SUM(CASE WHEN A.DIRECTION='IN' AND A.SALES_CHANNEL_CD='P2R CALL CEN' THEN 1 ELSE 0 END) P2RCALLCEN_I,
 SUM(CASE WHEN A.DIRECTION='OUT' AND A.SALES_CHANNEL_CD='P2R CALL CEN' THEN 1 ELSE 0 END) P2RCALLCEN_O,
 SUM(CASE WHEN A.DIRECTION='IN' AND A.SALES_CHANNEL_CD='STORES' THEN 1 ELSE 0 END) STORES_I,
 SUM(CASE WHEN A.DIRECTION='OUT' AND A.SALES_CHANNEL_CD='STORES' THEN 1 ELSE 0 END) STORES_O,
 SUM(CASE WHEN A.DIRECTION='IN' AND A.SALES_CHANNEL_CD='TE' THEN 1 ELSE 0 END) TE_I,
 SUM(CASE WHEN A.DIRECTION='OUT' AND A.SALES_CHANNEL_CD='TE' THEN 1 ELSE 0 END) TE_O,
 SUM(CASE WHEN A.DIRECTION='IN' AND A.SALES_CHANNEL_CD='WEB' THEN 1 ELSE 0 END) WEB_I,
 SUM(CASE WHEN A.DIRECTION='OUT' AND A.SALES_CHANNEL_CD='WEB' THEN 1 ELSE 0 END) WEB_O,
 TOT_I-(ALTCHNL_I+COR_I+CS_I+D2D_I+MOBILITYDMD_I+NATIONALRET_I+OBTM_I+ONLINE_I+P2RCALLCEN_I+STORES_I+TE_I+WEB_I) OTH_I,
 TOT_O-(ALTCHNL_O+COR_O+CS_O+D2D_O+MOBILITYDMD_O+NATIONALRET_O+OBTM_O+ONLINE_O+P2RCALLCEN_O+STORES_O+TE_O+WEB_O) OTH_O,
 SUM(CASE WHEN A.DIRECTION='IN' THEN 1 ELSE 0 END) TOT_I,
 SUM(CASE WHEN A.DIRECTION='OUT' THEN 1 ELSE 0 END) TOT_O,
 D.YR, D.MN
FROM MIS.ACC_RAW A
INNER JOIN MIS.ACC_CAL2  D ON A.START_DT = D.DT
INNER JOIN MIS.ACC_BASE_DATA B ON A.TRAC_ID=B.TRAC_ID
GROUP BY 1, D.YR, D.MN,2,3) Y ON Z.AREA_MGR=Y.AREA_MGR AND Z.PRODUCT=Y.PRODUCT AND Z.FEATURE=Y.FEATURE
) sums
WHERE TOT>0
GROUP	BY 1, YR, MN, ROLLUP (2,3)
ORDER	BY 1, YR DESC,MN DESC,2,3,4
;

--5
SELECT	
ACCT_MGR,PRODUCT,FEATURE, SUM(ALTCHNL_IN) ALTCHNL_IN, SUM(ALTCHNL_PCT) ALTCHNL_PCT,
		SUM(ALTCHNL_OUT) ALTCHNL_OUT, SUM(COR_IN) COR_IN, SUM(COR_PCT) COR_PCT,
		SUM(COR_OUT) COR_OUT, SUM(CS_IN) CS_IN, SUM(CS_PCT) CS_PCT, SUM(CS_OUT) CS_OUT,
		SUM(D2D_IN) D2D_IN, SUM(D2D_PCT) D2D_PCT, SUM(D2D_OUT) D2D_OUT,
		SUM(MOBILITYDMD_IN) MOBILITYDMD_IN, SUM(MOBILITYDMD_PCT) MOBILITYDMD_PCT,
		SUM(MOBILITYDMD_OUT) MOBILITYDMD_OUT, SUM(NATIONALRET_IN) NATIONALRET_IN,
		SUM(NATIONALRET_PCT) NATIONALRET_PCT, SUM(NATIONALRET_OUT) NATIONALRET_OUT,
		SUM(OBTM_IN) OBTM_IN, SUM(OBTM_PCT) OBTM_PCT, SUM(OBTM_OUT) OBTM_OUT,
		SUM(ONLINE_IN) ONLINE_IN, SUM(ONLINE_PCT) ONLINE_PCT, SUM(ONLINE_OUT) ONLINE_OUT,
		SUM(P2RCALLCEN_IN) P2RCALLCEN_IN, SUM(P2RCALLCEN_PCT) P2RCALLCEN_PCT,
		SUM(P2RCALLCEN_OUT) P2RCALLCEN_OUT, SUM(STORES_IN) STORES_IN,
		SUM(STORES_PCT) STORES_PCT, SUM(STORES_OUT) STORES_OUT, SUM(TE_IN) TE_IN,
		SUM(TE_PCT) TE_PCT, SUM(TE_OUT) TE_OUT, SUM(WEB_IN) WEB_IN, SUM(WEB_PCT) WEB_PCT,
		SUM(WEB_OUT) WEB_OUT, SUM(TOT) TOT, MAX(ROLLUP_TYPE) ROLLUP_TYPE, YR, MN
 
FROM	
(SELECT Z.ACCT_MGR, Z.PRODUCT,
CASE Z.FEATURE
 WHEN 'Expres' THEN 'Express'
 WHEN 'Hsia18' THEN 'Max18'
 WHEN 'BOT100' THEN 'VOIP 1000'
 WHEN 'BOT250' THEN 'VOIP 250'
 WHEN 'UNLMTD' THEN 'VOIP Unlimited'
 WHEN 'UFAM' THEN 'UFamily'
 WHEN 'U200la' THEN 'U200 Latino'
 WHEN 'Ubasic' THEN 'UBasic'
 WHEN 'Ucomp' THEN 'UComp'
 WHEN 'Udemo' THEN 'UDemo'
 ELSE Z.FEATURE
END FEATURE,
COALESCE(Y.ALTCHNL_I,0) ALTCHNL_IN, CASE WHEN TOT_I=0 THEN 0 ELSE CAST(1000*ALTCHNL_I/TOT_I AS DECIMAL(5,1))/10 END ALTCHNL_PCT, COALESCE(Y.ALTCHNL_O,0) ALTCHNL_OUT,
COALESCE(Y.COR_I,0) COR_IN, CASE WHEN TOT_I=0 THEN 0 ELSE CAST(1000*COR_I/TOT_I AS DECIMAL(5,1))/10 END COR_PCT, COALESCE(Y.COR_O,0) COR_OUT,
COALESCE(Y.CS_I,0) CS_IN, CASE WHEN TOT_I=0 THEN 0 ELSE CAST(1000*CS_I/TOT_I AS DECIMAL(5,1))/10 END CS_PCT, COALESCE(Y.CS_O,0) CS_OUT,
COALESCE(Y.D2D_I,0) D2D_IN, CASE WHEN TOT_I=0 THEN 0 ELSE CAST(1000*D2D_I/TOT_I AS DECIMAL(5,1))/10 END D2D_PCT, COALESCE(Y.D2D_O,0) D2D_OUT,
COALESCE(Y.MOBILITYDMD_I,0) MOBILITYDMD_IN, CASE WHEN TOT_I=0 THEN 0 ELSE CAST(1000*MOBILITYDMD_I/TOT_I AS DECIMAL(5,1))/10 END MOBILITYDMD_PCT, COALESCE(Y.MOBILITYDMD_O,0) MOBILITYDMD_OUT,
COALESCE(Y.NATIONALRET_I,0) NATIONALRET_IN, CASE WHEN TOT_I=0 THEN 0 ELSE CAST(1000*NATIONALRET_I/TOT_I AS DECIMAL(5,1))/10 END NATIONALRET_PCT, COALESCE(Y.NATIONALRET_O,0) NATIONALRET_OUT,
COALESCE(Y.OBTM_I,0) OBTM_IN, CASE WHEN TOT_I=0 THEN 0 ELSE CAST(1000*OBTM_I/TOT_I AS DECIMAL(5,1))/10 END OBTM_PCT, COALESCE(Y.OBTM_O,0) OBTM_OUT,
COALESCE(Y.ONLINE_I,0) ONLINE_IN, CASE WHEN TOT_I=0 THEN 0 ELSE CAST(1000*ONLINE_I/TOT_I AS DECIMAL(5,1))/10 END ONLINE_PCT, COALESCE(Y.ONLINE_O,0) ONLINE_OUT,
COALESCE(Y.P2RCALLCEN_I,0) P2RCALLCEN_IN, CASE WHEN TOT_I=0 THEN 0 ELSE CAST(1000*P2RCALLCEN_I/TOT_I AS DECIMAL(5,1))/10 END P2RCALLCEN_PCT, COALESCE(Y.P2RCALLCEN_O,0) P2RCALLCEN_OUT,
COALESCE(Y.STORES_I,0) STORES_IN, CASE WHEN TOT_I=0 THEN 0 ELSE CAST(1000*STORES_I/TOT_I AS DECIMAL(5,1))/10 END STORES_PCT, COALESCE(Y.STORES_O,0) STORES_OUT,
COALESCE(Y.TE_I,0) TE_IN, CASE WHEN TOT_I=0 THEN 0 ELSE CAST(1000*TE_I/TOT_I AS DECIMAL(5,1))/10 END TE_PCT, COALESCE(Y.TE_O,0) TE_OUT,
COALESCE(Y.WEB_I,0) WEB_IN, CASE WHEN TOT_I=0 THEN 0 ELSE CAST(1000*WEB_I/TOT_I AS DECIMAL(5,1))/10 END WEB_PCT, COALESCE(Y.WEB_O,0) WEB_OUT,
COALESCE(TOT_I,0)  + COALESCE(TOT_O,0)  TOT,
'ACCT_MGR' ROLLUP_TYPE, YR, MN
FROM (
SELECT B.ACCT_MGR, A.PRODUCT, A.FEATURE 
FROM (SELECT PRODUCT, FEATURE FROM MIS.ACC_RAW WHERE FEATURE NOT IN ('000','BVSTAN') GROUP BY 1,2) A,
     (SELECT ACCT_MGR FROM MIS.ACC_BASE_DATA WHERE ACCT_MGR GT'' GROUP BY 1) B) Z
LEFT OUTER JOIN (
SELECT B.ACCT_MGR, A.PRODUCT, A.FEATURE,
 SUM(CASE WHEN A.DIRECTION='IN' AND A.SALES_CHANNEL_CD='ALT CHNL' THEN 1 ELSE 0 END) ALTCHNL_I,
 SUM(CASE WHEN A.DIRECTION='OUT' AND A.SALES_CHANNEL_CD='ALT CHNL' THEN 1 ELSE 0 END) ALTCHNL_O,
 SUM(CASE WHEN A.DIRECTION='IN' AND A.SALES_CHANNEL_CD='COR' THEN 1 ELSE 0 END) COR_I,
 SUM(CASE WHEN A.DIRECTION='OUT' AND A.SALES_CHANNEL_CD='COR' THEN 1 ELSE 0 END) COR_O,
 SUM(CASE WHEN A.DIRECTION='IN' AND A.SALES_CHANNEL_CD='CS' THEN 1 ELSE 0 END) CS_I,
 SUM(CASE WHEN A.DIRECTION='OUT' AND A.SALES_CHANNEL_CD='CS' THEN 1 ELSE 0 END) CS_O,
 SUM(CASE WHEN A.DIRECTION='IN' AND A.SALES_CHANNEL_CD='D2D' THEN 1 ELSE 0 END) D2D_I,
 SUM(CASE WHEN A.DIRECTION='OUT' AND A.SALES_CHANNEL_CD='D2D' THEN 1 ELSE 0 END) D2D_O,
 SUM(CASE WHEN A.DIRECTION='IN' AND A.SALES_CHANNEL_CD='MOBILITY DMD' THEN 1 ELSE 0 END) MOBILITYDMD_I,
 SUM(CASE WHEN A.DIRECTION='OUT' AND A.SALES_CHANNEL_CD='MOBILITY DMD' THEN 1 ELSE 0 END) MOBILITYDMD_O,
 SUM(CASE WHEN A.DIRECTION='IN' AND A.SALES_CHANNEL_CD='NATIONAL RET' THEN 1 ELSE 0 END) NATIONALRET_I,
 SUM(CASE WHEN A.DIRECTION='OUT' AND A.SALES_CHANNEL_CD='NATIONAL RET' THEN 1 ELSE 0 END) NATIONALRET_O,
 SUM(CASE WHEN A.DIRECTION='IN' AND A.SALES_CHANNEL_CD='OBTM' THEN 1 ELSE 0 END) OBTM_I,
 SUM(CASE WHEN A.DIRECTION='OUT' AND A.SALES_CHANNEL_CD='OBTM' THEN 1 ELSE 0 END) OBTM_O,
 SUM(CASE WHEN A.DIRECTION='IN' AND A.SALES_CHANNEL_CD='ONLINE' THEN 1 ELSE 0 END) ONLINE_I,
 SUM(CASE WHEN A.DIRECTION='OUT' AND A.SALES_CHANNEL_CD='ONLINE' THEN 1 ELSE 0 END) ONLINE_O,
 SUM(CASE WHEN A.DIRECTION='IN' AND A.SALES_CHANNEL_CD='P2R CALL CEN' THEN 1 ELSE 0 END) P2RCALLCEN_I,
 SUM(CASE WHEN A.DIRECTION='OUT' AND A.SALES_CHANNEL_CD='P2R CALL CEN' THEN 1 ELSE 0 END) P2RCALLCEN_O,
 SUM(CASE WHEN A.DIRECTION='IN' AND A.SALES_CHANNEL_CD='STORES' THEN 1 ELSE 0 END) STORES_I,
 SUM(CASE WHEN A.DIRECTION='OUT' AND A.SALES_CHANNEL_CD='STORES' THEN 1 ELSE 0 END) STORES_O,
 SUM(CASE WHEN A.DIRECTION='IN' AND A.SALES_CHANNEL_CD='TE' THEN 1 ELSE 0 END) TE_I,
 SUM(CASE WHEN A.DIRECTION='OUT' AND A.SALES_CHANNEL_CD='TE' THEN 1 ELSE 0 END) TE_O,
 SUM(CASE WHEN A.DIRECTION='IN' AND A.SALES_CHANNEL_CD='WEB' THEN 1 ELSE 0 END) WEB_I,
 SUM(CASE WHEN A.DIRECTION='OUT' AND A.SALES_CHANNEL_CD='WEB' THEN 1 ELSE 0 END) WEB_O,
 TOT_I-(ALTCHNL_I+COR_I+CS_I+D2D_I+MOBILITYDMD_I+NATIONALRET_I+OBTM_I+ONLINE_I+P2RCALLCEN_I+STORES_I+TE_I+WEB_I) OTH_I,
 TOT_O-(ALTCHNL_O+COR_O+CS_O+D2D_O+MOBILITYDMD_O+NATIONALRET_O+OBTM_O+ONLINE_O+P2RCALLCEN_O+STORES_O+TE_O+WEB_O) OTH_O,
 SUM(CASE WHEN A.DIRECTION='IN' THEN 1 ELSE 0 END) TOT_I,
 SUM(CASE WHEN A.DIRECTION='OUT' THEN 1 ELSE 0 END) TOT_O,
 D.YR, D.MN
FROM MIS.ACC_RAW A
INNER JOIN MIS.ACC_CAL2  D ON A.START_DT = D.DT
INNER JOIN MIS.ACC_BASE_DATA B ON A.TRAC_ID=B.TRAC_ID
GROUP BY 1, D.YR, D.MN,2,3) Y ON Z.ACCT_MGR=Y.ACCT_MGR AND Z.PRODUCT=Y.PRODUCT AND Z.FEATURE=Y.FEATURE
) sums
WHERE TOT>0
GROUP	BY 1, YR, MN, ROLLUP (2,3)
ORDER	BY 1, YR DESC,MN DESC,2,3,4
;

--6
SELECT	
PORTFOLIO,PRODUCT,FEATURE, SUM(ALTCHNL_IN) ALTCHNL_IN, SUM(ALTCHNL_PCT) ALTCHNL_PCT,
		SUM(ALTCHNL_OUT) ALTCHNL_OUT, SUM(COR_IN) COR_IN, SUM(COR_PCT) COR_PCT,
		SUM(COR_OUT) COR_OUT, SUM(CS_IN) CS_IN, SUM(CS_PCT) CS_PCT, SUM(CS_OUT) CS_OUT,
		SUM(D2D_IN) D2D_IN, SUM(D2D_PCT) D2D_PCT, SUM(D2D_OUT) D2D_OUT,
		SUM(MOBILITYDMD_IN) MOBILITYDMD_IN, SUM(MOBILITYDMD_PCT) MOBILITYDMD_PCT,
		SUM(MOBILITYDMD_OUT) MOBILITYDMD_OUT, SUM(NATIONALRET_IN) NATIONALRET_IN,
		SUM(NATIONALRET_PCT) NATIONALRET_PCT, SUM(NATIONALRET_OUT) NATIONALRET_OUT,
		SUM(OBTM_IN) OBTM_IN, SUM(OBTM_PCT) OBTM_PCT, SUM(OBTM_OUT) OBTM_OUT,
		SUM(ONLINE_IN) ONLINE_IN, SUM(ONLINE_PCT) ONLINE_PCT, SUM(ONLINE_OUT) ONLINE_OUT,
		SUM(P2RCALLCEN_IN) P2RCALLCEN_IN, SUM(P2RCALLCEN_PCT) P2RCALLCEN_PCT,
		SUM(P2RCALLCEN_OUT) P2RCALLCEN_OUT, SUM(STORES_IN) STORES_IN,
		SUM(STORES_PCT) STORES_PCT, SUM(STORES_OUT) STORES_OUT, SUM(TE_IN) TE_IN,
		SUM(TE_PCT) TE_PCT, SUM(TE_OUT) TE_OUT, SUM(WEB_IN) WEB_IN, SUM(WEB_PCT) WEB_PCT,
		SUM(WEB_OUT) WEB_OUT, SUM(TOT) TOT, MAX(ROLLUP_TYPE) ROLLUP_TYPE, YR, MN
 
FROM	
(SELECT Z.PORTFOLIO, Z.PRODUCT,
CASE Z.FEATURE
 WHEN 'Expres' THEN 'Express'
 WHEN 'Hsia18' THEN 'Max18'
 WHEN 'BOT100' THEN 'VOIP 1000'
 WHEN 'BOT250' THEN 'VOIP 250'
 WHEN 'UNLMTD' THEN 'VOIP Unlimited'
 WHEN 'UFAM' THEN 'UFamily'
 WHEN 'U200la' THEN 'U200 Latino'
 WHEN 'Ubasic' THEN 'UBasic'
 WHEN 'Ucomp' THEN 'UComp'
 WHEN 'Udemo' THEN 'UDemo'
 ELSE Z.FEATURE
END FEATURE,
COALESCE(Y.ALTCHNL_I,0) ALTCHNL_IN, CASE WHEN TOT_I=0 THEN 0 ELSE CAST(1000*ALTCHNL_I/TOT_I AS DECIMAL(5,1))/10 END ALTCHNL_PCT, COALESCE(Y.ALTCHNL_O,0) ALTCHNL_OUT,
COALESCE(Y.COR_I,0) COR_IN, CASE WHEN TOT_I=0 THEN 0 ELSE CAST(1000*COR_I/TOT_I AS DECIMAL(5,1))/10 END COR_PCT, COALESCE(Y.COR_O,0) COR_OUT,
COALESCE(Y.CS_I,0) CS_IN, CASE WHEN TOT_I=0 THEN 0 ELSE CAST(1000*CS_I/TOT_I AS DECIMAL(5,1))/10 END CS_PCT, COALESCE(Y.CS_O,0) CS_OUT,
COALESCE(Y.D2D_I,0) D2D_IN, CASE WHEN TOT_I=0 THEN 0 ELSE CAST(1000*D2D_I/TOT_I AS DECIMAL(5,1))/10 END D2D_PCT, COALESCE(Y.D2D_O,0) D2D_OUT,
COALESCE(Y.MOBILITYDMD_I,0) MOBILITYDMD_IN, CASE WHEN TOT_I=0 THEN 0 ELSE CAST(1000*MOBILITYDMD_I/TOT_I AS DECIMAL(5,1))/10 END MOBILITYDMD_PCT, COALESCE(Y.MOBILITYDMD_O,0) MOBILITYDMD_OUT,
COALESCE(Y.NATIONALRET_I,0) NATIONALRET_IN, CASE WHEN TOT_I=0 THEN 0 ELSE CAST(1000*NATIONALRET_I/TOT_I AS DECIMAL(5,1))/10 END NATIONALRET_PCT, COALESCE(Y.NATIONALRET_O,0) NATIONALRET_OUT,
COALESCE(Y.OBTM_I,0) OBTM_IN, CASE WHEN TOT_I=0 THEN 0 ELSE CAST(1000*OBTM_I/TOT_I AS DECIMAL(5,1))/10 END OBTM_PCT, COALESCE(Y.OBTM_O,0) OBTM_OUT,
COALESCE(Y.ONLINE_I,0) ONLINE_IN, CASE WHEN TOT_I=0 THEN 0 ELSE CAST(1000*ONLINE_I/TOT_I AS DECIMAL(5,1))/10 END ONLINE_PCT, COALESCE(Y.ONLINE_O,0) ONLINE_OUT,
COALESCE(Y.P2RCALLCEN_I,0) P2RCALLCEN_IN, CASE WHEN TOT_I=0 THEN 0 ELSE CAST(1000*P2RCALLCEN_I/TOT_I AS DECIMAL(5,1))/10 END P2RCALLCEN_PCT, COALESCE(Y.P2RCALLCEN_O,0) P2RCALLCEN_OUT,
COALESCE(Y.STORES_I,0) STORES_IN, CASE WHEN TOT_I=0 THEN 0 ELSE CAST(1000*STORES_I/TOT_I AS DECIMAL(5,1))/10 END STORES_PCT, COALESCE(Y.STORES_O,0) STORES_OUT,
COALESCE(Y.TE_I,0) TE_IN, CASE WHEN TOT_I=0 THEN 0 ELSE CAST(1000*TE_I/TOT_I AS DECIMAL(5,1))/10 END TE_PCT, COALESCE(Y.TE_O,0) TE_OUT,
COALESCE(Y.WEB_I,0) WEB_IN, CASE WHEN TOT_I=0 THEN 0 ELSE CAST(1000*WEB_I/TOT_I AS DECIMAL(5,1))/10 END WEB_PCT, COALESCE(Y.WEB_O,0) WEB_OUT,
COALESCE(TOT_I,0)  + COALESCE(TOT_O,0)  TOT,
'PORTFOLIO' ROLLUP_TYPE, YR, MN
FROM (
SELECT B.PORTFOLIO, A.PRODUCT, A.FEATURE 
FROM (SELECT PRODUCT, FEATURE FROM MIS.ACC_RAW WHERE FEATURE NOT IN ('000','BVSTAN') GROUP BY 1,2) A,
     (SELECT PORTFOLIO FROM MIS.ACC_BASE_DATA WHERE PORTFOLIO GT'' GROUP BY 1) B) Z
LEFT OUTER JOIN (
SELECT B.PORTFOLIO, A.PRODUCT, A.FEATURE,
 SUM(CASE WHEN A.DIRECTION='IN' AND A.SALES_CHANNEL_CD='ALT CHNL' THEN 1 ELSE 0 END) ALTCHNL_I,
 SUM(CASE WHEN A.DIRECTION='OUT' AND A.SALES_CHANNEL_CD='ALT CHNL' THEN 1 ELSE 0 END) ALTCHNL_O,
 SUM(CASE WHEN A.DIRECTION='IN' AND A.SALES_CHANNEL_CD='COR' THEN 1 ELSE 0 END) COR_I,
 SUM(CASE WHEN A.DIRECTION='OUT' AND A.SALES_CHANNEL_CD='COR' THEN 1 ELSE 0 END) COR_O,
 SUM(CASE WHEN A.DIRECTION='IN' AND A.SALES_CHANNEL_CD='CS' THEN 1 ELSE 0 END) CS_I,
 SUM(CASE WHEN A.DIRECTION='OUT' AND A.SALES_CHANNEL_CD='CS' THEN 1 ELSE 0 END) CS_O,
 SUM(CASE WHEN A.DIRECTION='IN' AND A.SALES_CHANNEL_CD='D2D' THEN 1 ELSE 0 END) D2D_I,
 SUM(CASE WHEN A.DIRECTION='OUT' AND A.SALES_CHANNEL_CD='D2D' THEN 1 ELSE 0 END) D2D_O,
 SUM(CASE WHEN A.DIRECTION='IN' AND A.SALES_CHANNEL_CD='MOBILITY DMD' THEN 1 ELSE 0 END) MOBILITYDMD_I,
 SUM(CASE WHEN A.DIRECTION='OUT' AND A.SALES_CHANNEL_CD='MOBILITY DMD' THEN 1 ELSE 0 END) MOBILITYDMD_O,
 SUM(CASE WHEN A.DIRECTION='IN' AND A.SALES_CHANNEL_CD='NATIONAL RET' THEN 1 ELSE 0 END) NATIONALRET_I,
 SUM(CASE WHEN A.DIRECTION='OUT' AND A.SALES_CHANNEL_CD='NATIONAL RET' THEN 1 ELSE 0 END) NATIONALRET_O,
 SUM(CASE WHEN A.DIRECTION='IN' AND A.SALES_CHANNEL_CD='OBTM' THEN 1 ELSE 0 END) OBTM_I,
 SUM(CASE WHEN A.DIRECTION='OUT' AND A.SALES_CHANNEL_CD='OBTM' THEN 1 ELSE 0 END) OBTM_O,
 SUM(CASE WHEN A.DIRECTION='IN' AND A.SALES_CHANNEL_CD='ONLINE' THEN 1 ELSE 0 END) ONLINE_I,
 SUM(CASE WHEN A.DIRECTION='OUT' AND A.SALES_CHANNEL_CD='ONLINE' THEN 1 ELSE 0 END) ONLINE_O,
 SUM(CASE WHEN A.DIRECTION='IN' AND A.SALES_CHANNEL_CD='P2R CALL CEN' THEN 1 ELSE 0 END) P2RCALLCEN_I,
 SUM(CASE WHEN A.DIRECTION='OUT' AND A.SALES_CHANNEL_CD='P2R CALL CEN' THEN 1 ELSE 0 END) P2RCALLCEN_O,
 SUM(CASE WHEN A.DIRECTION='IN' AND A.SALES_CHANNEL_CD='STORES' THEN 1 ELSE 0 END) STORES_I,
 SUM(CASE WHEN A.DIRECTION='OUT' AND A.SALES_CHANNEL_CD='STORES' THEN 1 ELSE 0 END) STORES_O,
 SUM(CASE WHEN A.DIRECTION='IN' AND A.SALES_CHANNEL_CD='TE' THEN 1 ELSE 0 END) TE_I,
 SUM(CASE WHEN A.DIRECTION='OUT' AND A.SALES_CHANNEL_CD='TE' THEN 1 ELSE 0 END) TE_O,
 SUM(CASE WHEN A.DIRECTION='IN' AND A.SALES_CHANNEL_CD='WEB' THEN 1 ELSE 0 END) WEB_I,
 SUM(CASE WHEN A.DIRECTION='OUT' AND A.SALES_CHANNEL_CD='WEB' THEN 1 ELSE 0 END) WEB_O,
 TOT_I-(ALTCHNL_I+COR_I+CS_I+D2D_I+MOBILITYDMD_I+NATIONALRET_I+OBTM_I+ONLINE_I+P2RCALLCEN_I+STORES_I+TE_I+WEB_I) OTH_I,
 TOT_O-(ALTCHNL_O+COR_O+CS_O+D2D_O+MOBILITYDMD_O+NATIONALRET_O+OBTM_O+ONLINE_O+P2RCALLCEN_O+STORES_O+TE_O+WEB_O) OTH_O,
 SUM(CASE WHEN A.DIRECTION='IN' THEN 1 ELSE 0 END) TOT_I,
 SUM(CASE WHEN A.DIRECTION='OUT' THEN 1 ELSE 0 END) TOT_O,
 D.YR, D.MN
FROM MIS.ACC_RAW A
INNER JOIN MIS.ACC_CAL2  D ON A.START_DT = D.DT
INNER JOIN MIS.ACC_BASE_DATA B ON A.TRAC_ID=B.TRAC_ID
GROUP BY 1, D.YR, D.MN,2,3) Y ON Z.PORTFOLIO=Y.PORTFOLIO AND Z.PRODUCT=Y.PRODUCT AND Z.FEATURE=Y.FEATURE
) sums
WHERE TOT>0
GROUP	BY 1, YR, MN, ROLLUP (2,3)
ORDER	BY 1, YR DESC,MN DESC,2,3,4
;

--7
SELECT	
TRAC_ID,PRODUCT,FEATURE, SUM(ALTCHNL_IN) ALTCHNL_IN, SUM(ALTCHNL_PCT) ALTCHNL_PCT,
		SUM(ALTCHNL_OUT) ALTCHNL_OUT, SUM(COR_IN) COR_IN, SUM(COR_PCT) COR_PCT,
		SUM(COR_OUT) COR_OUT, SUM(CS_IN) CS_IN, SUM(CS_PCT) CS_PCT, SUM(CS_OUT) CS_OUT,
		SUM(D2D_IN) D2D_IN, SUM(D2D_PCT) D2D_PCT, SUM(D2D_OUT) D2D_OUT,
		SUM(MOBILITYDMD_IN) MOBILITYDMD_IN, SUM(MOBILITYDMD_PCT) MOBILITYDMD_PCT,
		SUM(MOBILITYDMD_OUT) MOBILITYDMD_OUT, SUM(NATIONALRET_IN) NATIONALRET_IN,
		SUM(NATIONALRET_PCT) NATIONALRET_PCT, SUM(NATIONALRET_OUT) NATIONALRET_OUT,
		SUM(OBTM_IN) OBTM_IN, SUM(OBTM_PCT) OBTM_PCT, SUM(OBTM_OUT) OBTM_OUT,
		SUM(ONLINE_IN) ONLINE_IN, SUM(ONLINE_PCT) ONLINE_PCT, SUM(ONLINE_OUT) ONLINE_OUT,
		SUM(P2RCALLCEN_IN) P2RCALLCEN_IN, SUM(P2RCALLCEN_PCT) P2RCALLCEN_PCT,
		SUM(P2RCALLCEN_OUT) P2RCALLCEN_OUT, SUM(STORES_IN) STORES_IN,
		SUM(STORES_PCT) STORES_PCT, SUM(STORES_OUT) STORES_OUT, SUM(TE_IN) TE_IN,
		SUM(TE_PCT) TE_PCT, SUM(TE_OUT) TE_OUT, SUM(WEB_IN) WEB_IN, SUM(WEB_PCT) WEB_PCT,
		SUM(WEB_OUT) WEB_OUT, SUM(TOT) TOT, MAX(ROLLUP_TYPE) ROLLUP_TYPE, YR, MN
 
FROM	
(SELECT Z.TRAC_ID, Z.PRODUCT,
CASE Z.FEATURE
 WHEN 'Expres' THEN 'Express'
 WHEN 'Hsia18' THEN 'Max18'
 WHEN 'BOT100' THEN 'VOIP 1000'
 WHEN 'BOT250' THEN 'VOIP 250'
 WHEN 'UNLMTD' THEN 'VOIP Unlimited'
 WHEN 'UFAM' THEN 'UFamily'
 WHEN 'U200la' THEN 'U200 Latino'
 WHEN 'Ubasic' THEN 'UBasic'
 WHEN 'Ucomp' THEN 'UComp'
 WHEN 'Udemo' THEN 'UDemo'
 ELSE Z.FEATURE
END FEATURE,
COALESCE(Y.ALTCHNL_I,0) ALTCHNL_IN, CASE WHEN TOT_I=0 THEN 0 ELSE CAST(1000*ALTCHNL_I/TOT_I AS DECIMAL(5,1))/10 END ALTCHNL_PCT, COALESCE(Y.ALTCHNL_O,0) ALTCHNL_OUT,
COALESCE(Y.COR_I,0) COR_IN, CASE WHEN TOT_I=0 THEN 0 ELSE CAST(1000*COR_I/TOT_I AS DECIMAL(5,1))/10 END COR_PCT, COALESCE(Y.COR_O,0) COR_OUT,
COALESCE(Y.CS_I,0) CS_IN, CASE WHEN TOT_I=0 THEN 0 ELSE CAST(1000*CS_I/TOT_I AS DECIMAL(5,1))/10 END CS_PCT, COALESCE(Y.CS_O,0) CS_OUT,
COALESCE(Y.D2D_I,0) D2D_IN, CASE WHEN TOT_I=0 THEN 0 ELSE CAST(1000*D2D_I/TOT_I AS DECIMAL(5,1))/10 END D2D_PCT, COALESCE(Y.D2D_O,0) D2D_OUT,
COALESCE(Y.MOBILITYDMD_I,0) MOBILITYDMD_IN, CASE WHEN TOT_I=0 THEN 0 ELSE CAST(1000*MOBILITYDMD_I/TOT_I AS DECIMAL(5,1))/10 END MOBILITYDMD_PCT, COALESCE(Y.MOBILITYDMD_O,0) MOBILITYDMD_OUT,
COALESCE(Y.NATIONALRET_I,0) NATIONALRET_IN, CASE WHEN TOT_I=0 THEN 0 ELSE CAST(1000*NATIONALRET_I/TOT_I AS DECIMAL(5,1))/10 END NATIONALRET_PCT, COALESCE(Y.NATIONALRET_O,0) NATIONALRET_OUT,
COALESCE(Y.OBTM_I,0) OBTM_IN, CASE WHEN TOT_I=0 THEN 0 ELSE CAST(1000*OBTM_I/TOT_I AS DECIMAL(5,1))/10 END OBTM_PCT, COALESCE(Y.OBTM_O,0) OBTM_OUT,
COALESCE(Y.ONLINE_I,0) ONLINE_IN, CASE WHEN TOT_I=0 THEN 0 ELSE CAST(1000*ONLINE_I/TOT_I AS DECIMAL(5,1))/10 END ONLINE_PCT, COALESCE(Y.ONLINE_O,0) ONLINE_OUT,
COALESCE(Y.P2RCALLCEN_I,0) P2RCALLCEN_IN, CASE WHEN TOT_I=0 THEN 0 ELSE CAST(1000*P2RCALLCEN_I/TOT_I AS DECIMAL(5,1))/10 END P2RCALLCEN_PCT, COALESCE(Y.P2RCALLCEN_O,0) P2RCALLCEN_OUT,
COALESCE(Y.STORES_I,0) STORES_IN, CASE WHEN TOT_I=0 THEN 0 ELSE CAST(1000*STORES_I/TOT_I AS DECIMAL(5,1))/10 END STORES_PCT, COALESCE(Y.STORES_O,0) STORES_OUT,
COALESCE(Y.TE_I,0) TE_IN, CASE WHEN TOT_I=0 THEN 0 ELSE CAST(1000*TE_I/TOT_I AS DECIMAL(5,1))/10 END TE_PCT, COALESCE(Y.TE_O,0) TE_OUT,
COALESCE(Y.WEB_I,0) WEB_IN, CASE WHEN TOT_I=0 THEN 0 ELSE CAST(1000*WEB_I/TOT_I AS DECIMAL(5,1))/10 END WEB_PCT, COALESCE(Y.WEB_O,0) WEB_OUT,
COALESCE(TOT_I,0)  + COALESCE(TOT_O,0)  TOT,
'TRAC_ID' ROLLUP_TYPE, YR, MN
FROM (
SELECT B.TRAC_ID, A.PRODUCT, A.FEATURE 
FROM (SELECT PRODUCT, FEATURE FROM MIS.ACC_RAW WHERE FEATURE NOT IN ('000','BVSTAN') GROUP BY 1,2) A,
     (SELECT TRAC_ID FROM MIS.ACC_BASE_DATA WHERE TRAC_ID GT'' GROUP BY 1) B) Z
LEFT OUTER JOIN (
SELECT B.TRAC_ID, A.PRODUCT, A.FEATURE,
 SUM(CASE WHEN A.DIRECTION='IN' AND A.SALES_CHANNEL_CD='ALT CHNL' THEN 1 ELSE 0 END) ALTCHNL_I,
 SUM(CASE WHEN A.DIRECTION='OUT' AND A.SALES_CHANNEL_CD='ALT CHNL' THEN 1 ELSE 0 END) ALTCHNL_O,
 SUM(CASE WHEN A.DIRECTION='IN' AND A.SALES_CHANNEL_CD='COR' THEN 1 ELSE 0 END) COR_I,
 SUM(CASE WHEN A.DIRECTION='OUT' AND A.SALES_CHANNEL_CD='COR' THEN 1 ELSE 0 END) COR_O,
 SUM(CASE WHEN A.DIRECTION='IN' AND A.SALES_CHANNEL_CD='CS' THEN 1 ELSE 0 END) CS_I,
 SUM(CASE WHEN A.DIRECTION='OUT' AND A.SALES_CHANNEL_CD='CS' THEN 1 ELSE 0 END) CS_O,
 SUM(CASE WHEN A.DIRECTION='IN' AND A.SALES_CHANNEL_CD='D2D' THEN 1 ELSE 0 END) D2D_I,
 SUM(CASE WHEN A.DIRECTION='OUT' AND A.SALES_CHANNEL_CD='D2D' THEN 1 ELSE 0 END) D2D_O,
 SUM(CASE WHEN A.DIRECTION='IN' AND A.SALES_CHANNEL_CD='MOBILITY DMD' THEN 1 ELSE 0 END) MOBILITYDMD_I,
 SUM(CASE WHEN A.DIRECTION='OUT' AND A.SALES_CHANNEL_CD='MOBILITY DMD' THEN 1 ELSE 0 END) MOBILITYDMD_O,
 SUM(CASE WHEN A.DIRECTION='IN' AND A.SALES_CHANNEL_CD='NATIONAL RET' THEN 1 ELSE 0 END) NATIONALRET_I,
 SUM(CASE WHEN A.DIRECTION='OUT' AND A.SALES_CHANNEL_CD='NATIONAL RET' THEN 1 ELSE 0 END) NATIONALRET_O,
 SUM(CASE WHEN A.DIRECTION='IN' AND A.SALES_CHANNEL_CD='OBTM' THEN 1 ELSE 0 END) OBTM_I,
 SUM(CASE WHEN A.DIRECTION='OUT' AND A.SALES_CHANNEL_CD='OBTM' THEN 1 ELSE 0 END) OBTM_O,
 SUM(CASE WHEN A.DIRECTION='IN' AND A.SALES_CHANNEL_CD='ONLINE' THEN 1 ELSE 0 END) ONLINE_I,
 SUM(CASE WHEN A.DIRECTION='OUT' AND A.SALES_CHANNEL_CD='ONLINE' THEN 1 ELSE 0 END) ONLINE_O,
 SUM(CASE WHEN A.DIRECTION='IN' AND A.SALES_CHANNEL_CD='P2R CALL CEN' THEN 1 ELSE 0 END) P2RCALLCEN_I,
 SUM(CASE WHEN A.DIRECTION='OUT' AND A.SALES_CHANNEL_CD='P2R CALL CEN' THEN 1 ELSE 0 END) P2RCALLCEN_O,
 SUM(CASE WHEN A.DIRECTION='IN' AND A.SALES_CHANNEL_CD='STORES' THEN 1 ELSE 0 END) STORES_I,
 SUM(CASE WHEN A.DIRECTION='OUT' AND A.SALES_CHANNEL_CD='STORES' THEN 1 ELSE 0 END) STORES_O,
 SUM(CASE WHEN A.DIRECTION='IN' AND A.SALES_CHANNEL_CD='TE' THEN 1 ELSE 0 END) TE_I,
 SUM(CASE WHEN A.DIRECTION='OUT' AND A.SALES_CHANNEL_CD='TE' THEN 1 ELSE 0 END) TE_O,
 SUM(CASE WHEN A.DIRECTION='IN' AND A.SALES_CHANNEL_CD='WEB' THEN 1 ELSE 0 END) WEB_I,
 SUM(CASE WHEN A.DIRECTION='OUT' AND A.SALES_CHANNEL_CD='WEB' THEN 1 ELSE 0 END) WEB_O,
 TOT_I-(ALTCHNL_I+COR_I+CS_I+D2D_I+MOBILITYDMD_I+NATIONALRET_I+OBTM_I+ONLINE_I+P2RCALLCEN_I+STORES_I+TE_I+WEB_I) OTH_I,
 TOT_O-(ALTCHNL_O+COR_O+CS_O+D2D_O+MOBILITYDMD_O+NATIONALRET_O+OBTM_O+ONLINE_O+P2RCALLCEN_O+STORES_O+TE_O+WEB_O) OTH_O,
 SUM(CASE WHEN A.DIRECTION='IN' THEN 1 ELSE 0 END) TOT_I,
 SUM(CASE WHEN A.DIRECTION='OUT' THEN 1 ELSE 0 END) TOT_O,
 D.YR, D.MN
FROM MIS.ACC_RAW A
INNER JOIN MIS.ACC_CAL2  D ON A.START_DT = D.DT
INNER JOIN MIS.ACC_BASE_DATA B ON A.TRAC_ID=B.TRAC_ID
GROUP BY 1, D.YR, D.MN,2,3) Y ON Z.TRAC_ID=Y.TRAC_ID AND Z.PRODUCT=Y.PRODUCT AND Z.FEATURE=Y.FEATURE
) sums
WHERE TOT>0
GROUP	BY 1, YR, MN, ROLLUP (2,3)
ORDER	BY 1, YR DESC,MN DESC,2,3,4
;